﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using CrystalDecisions.CrystalReports.Engine;
using Microsoft.Reporting.WebForms;
namespace VSM
{


    public partial class rptViewer : System.Web.UI.Page
    {

        private static string rptType;
        private static string GeneralID;
        protected void Page_Load(object sender, EventArgs e)
        {
            
            
            if (!IsPostBack) {

                rptType = Request.QueryString["type"] == null ? "" : Request.QueryString["type"];
                GeneralID = Request.QueryString["GeneralID"] == null ? "" : Request.QueryString["GeneralID"];
                hf_ResID.Value = Request.QueryString["ResID"] == null ? "-1" : Request.QueryString["ResID"];
                hf_VIN.Value = Request.QueryString["VIN"] == null ? "" : Request.QueryString["VIN"];
                hf_GeneralID.Value = GeneralID;
                string reportPath = "";
                DataTable mainDT = new DataTable();
                DataTable laboutDT = new DataTable();
                if (rptType == "")
                    return;
                switch (rptType) { 
                    case "QT":
                        reportPath = "/Business/Service/Quotation.rpt";
                        break;
                    case "RE":
                        reportPath = "/Business/Service/AppointmentSheet.rpt";
                        break;
                    case "RO":
                        reportPath = "/Business/Service/Jobcard.rpt";
                        break;
                    case "PO":
                        reportPath = "/Business/Service/PartOrder.rpt";
                        break;
                    case "RS":
                        reportPath = "/Business/Service/ReceptionSheet.rpt";
                        break;
                    case "WS":
                        reportPath = "/Business/Service/WSCFormA.rpt";
                        break;
                    case "QI":
                        reportPath = "/Business/Service/QuickInspection.rpt";
                        break;
                    case "WB":
                        reportPath = "/Business/Service/WSCFormB.rpt";
                        break;
                    case "PQR":
                        reportPath = "/Business/Service/QPR.rpt";
                        break;
                    case "RES":
                        reportPath = "/Business/Service/AppointmentSheet.rpt";
                        break;
                    case "SH":
                        reportPath = "/Business/Service/ServiceHistory.rpt";
                        break;
                }
                ReportDocument rptDoc = new ReportDocument();

                CrystalReportViewer1.PrintMode = CrystalDecisions.Web.PrintMode.ActiveX;
                CrystalReportViewer1.ToolPanelView = CrystalDecisions.Web.ToolPanelViewType.None;
                        
                rptDoc.Load(Server.MapPath(reportPath));
                if (rptType == "RO" || rptType == "WS")
                {
                    rptDoc.Subreports[0].SetDataSource(getLabourCharge());
                }

                if (rptType == "QT" || rptType == "RO" || rptType == "PO" ||rptType == "QI")
                    rptDoc.SetDataSource(getGeneralData());

                else if (rptType == "RS")
                    rptDoc.SetDataSource(getReceptionSheet());
                else if (rptType == "WS") {
                    rptDoc.SetDataSource(getWSC());
                }
                else if (rptType == "WB") {
                    rptDoc.SetDataSource(getWSCB());
                }
                else if (rptType == "PQR")
                {
                    rptDoc.SetDataSource(getQPR());
                }

                else if (rptType == "RES") {
                    rptDoc.SetDataSource(getReservation());
                }
                else if (rptType == "SH") {
                    rptDoc.SetDataSource(getServiceHistory());
                }
                CrystalReportViewer1.ReportSource = rptDoc;
                CrystalReportViewer1.RefreshReport();
                CrystalReportViewer1.DataBind();

                //rptDoc.Subreports[0].set
                //CrystalReportViewer1.HasPrintButton = true;
                //ReportViewer1.ProcessingMode = ProcessingMode.Remote;

                //ServerReport serverReport = ReportViewer1.ServerReport;
                ////serverReport.LoadReportDefinition(Server.MapPath("/Business/Service/Quotation.rpt"));
                //// Set the report server URL and report path
                //serverReport.ReportServerUrl =
                //    new Uri(Server.MapPath("."));
                //serverReport.ReportPath = Server.MapPath("/Business/Service/Quotation.rpt");
                //ItemCategory = Request.QueryString["ItemCategory"] == null ? "" : Request.QueryString["ItemCategory"];
            }
            /*
            if (Session[Utils.AppConstants.ID_User] == null
                || Session[Utils.AppConstants.Password] == null
                || Session[Utils.AppConstants.ID_Dealer] == null)
            {
                Response.Redirect("Login.aspx");
            }
             * */
        }

        private string getDocumentType() {
            string sql = "select JobKind from U_GeneralService where GeneralID = " + GeneralID;
            Services sv = new Services();
            DataSet ds = sv.ExecuteQuery("DEALER", sql);
            
            return ds.Tables[0].Rows[0]["JobKind"].ToString();
        }
        private DataTable getGeneralData()
        {

            string sql = " select gs.JobID, gs.Receptionist, gs.AdviserCode, gs.TechnicianCode, jk.Name as JobKind, pm.Name as PaymentMethod,  ";
            sql += "gs.DateIn, gs.TimeIn, gs.ExpDelivery, gs.ExpTime, gs.StartDate, gs.StartTime,  ";
            sql += "gs.FinishDate, gs.FinishTime, gs.ActualDelivery, gs.ActualDeliveryTime, gs.CustomerRequest, gs.Notice,  ";
            sql += "gs.Odometer, gs.ServiceDate, gs.VIN, gs.Brand, gs.VehicleName, gs.EngineNo, gs.RegNo,  ";
            sql += "rn.U_KeyNo as KeyNo, '' as ProductionDate, '' as PDIDate, inv.DocDate as InvoiceDate, inv.U_DealerID as Dealer,  ";
            sql += "it.U_Color as ColorCode, gs.CustomerCode as CardCode, gs.CustomerName, rn.LotNumber as LotNo,  ";
            sql += "it.UserText as Remarks,  ms.Name as Model, gs.OtherName as CardFName,gs.CustomerName as CardName,  ";
            sql += "rd.E_Mail, gs.Tel as Phone1, rd.Fax, gs.Mobile as Cellular, gs.ContactPerson as U_ContactPerson,  ";
            sql += "gs.ContactPhone as U_ContactPhone, gs.Address , gs.TaxCode as LicTradNum, rd.Notes,  ";
            sql += "adv.Name AdviserName, tec.Name TechnicianName, ";
            sql += "sd.ItemCode, sd.ItemName, sd.FrgnName, sd.Quantity, sd.Price, sd.VAT, sd.DiscPrcnt, sd.Stock  ";
            sql += "from U_GeneralService gs left join U_GeneralServiceDetail sd on gs.GeneralID = sd.GeneralID  ";
            sql += "left join VSM_ORG..OCRD rd on rd.CardCode = gs.CustomerCode  ";
            sql += "left join VSM_ORG..[@JOBKINDSVC] jk on jk.Code = gs.JobKind ";
            sql += "left join VSM_ORG..[@PAYMENTMETHODSVC] pm on pm.Code = gs.Payment  ";
            sql += "left join OSRN rn on rn.DistNumber = gs.VIN ";
            sql += "left join INV1 inv1 on inv1.ItemCode = rn.ItemCode ";
            sql += "left join OITM it on it.ItemCode = inv1.ItemCode ";
            sql += "left join OINV inv on inv.DocEntry = inv1.BaseEntry ";
            sql += "left join [@ModelSales] ms on ms.Code = it.U_ModelSales ";
            sql += " left join VSM_ORG..[@TECHNICIAN] adv on gs.AdviserCode = adv.Code  ";
            sql += " left join VSM_ORG..[@TECHNICIAN] tec on gs.TechnicianCode = tec.Code  ";
            sql += "where gs.GeneralID = " + GeneralID + " AND sd.IsSelected = 1";
            //string sql = " select gs.JobID, gs.Receptionist, gs.AdviserCode, gs.TechnicianCode, jk.Name as JobKind, pm.Name as PaymentMethod, ";
            //sql += " gs.DateIn, gs.TimeIn, gs.ExpDelivery, gs.ExpTime, gs.StartDate, gs.StartTime, ";
            //sql += " gs.FinishDate, gs.FinishTime, gs.ActualDelivery, gs.ActualDeliveryTime, gs.CustomerRequest, gs.Notice, ";
            //sql += " gs.Odometer, gs.ServiceDate, ";
            //sql += " gs.VIN, gs.Brand, gs.VehicleName, gs.EngineNo, gs.RegNo, '' as KeyNo, '' as ProductionDate, '' as PDIDate, ";
            //sql += " sv.InvoiceDate, sv.Dealer, sv.ColorCode, sv.CustomerCode, sv.CustomerName, sv.LotNo, sv.Remarks, sv.Model, ";
            //sql += " rd.CardName, rd.CardFName, rd.E_Mail, rd.Phone1, rd.Fax, rd.Cellular, rd.U_ContactPerson, rd.U_ContactPhone, ";
            //sql += " rd.Address, rd.LicTradNum, rd.Notes, ";
            //sql += " sd.ItemCode, sd.ItemName, sd.FrgnName, sd.Quantity, sd.Price, sd.VAT, sd.DiscPrcnt, sd.Stock ";
            //sql += " from U_GeneralService gs join U_generalServiceDetail sd on gs.GeneralID = sd.GeneralID ";
            //sql += " join VSM_ORG..OCRD rd on rd.CardCode = gs.CustomerCode ";
            //sql += " left join VSM_ORG..[@JOBKINDSVC] jk on jk.Code = gs.JobKind";
            //sql += " left join VSM_ORG..[@PAYMENTMETHODSVC] pm on pm.Code = gs.Payment ";
            //sql += " where gs.GeneralID = " + GeneralID;

            Services sv = new Services();
            DataSet ds = sv.ExecuteQuery("DEALER", sql);
            return ds.Tables[0];
        }

        private DataTable getLabourCharge() {
            Services sv = new Services();
            string sql2 = "select ROW_NUMBER() OVER(ORDER BY LabourID DESC) AS RowNumber , gs.GeneralID, gs.LabourID, ";
            sql2 += " gs.FRTID, gs.PositionCode, gs.Quantity, gs.LabourCharge, frt.FRTName from U_GeneralServiceLabour gs ";
            sql2 += " join VSM_ORG..U_FRT frt on gs.FRTID = frt.FRTID where GeneralID = " + GeneralID;
            DataSet dsLabour = sv.ExecuteQuery("DEALER", sql2);
            return dsLabour.Tables[0];
        }

        private DataTable getReceptionSheet()
        {
           
            string sql = " select gs.JobID, gs.Receptionist, gs.AdviserCode, gs.TechnicianCode, jk.Name as JobKind, pm.Name as PaymentMethod,  ";
            sql += "gs.DateIn, gs.TimeIn, gs.ExpDelivery, gs.ExpTime, gs.StartDate, gs.StartTime,  ";
            sql += "gs.FinishDate, gs.FinishTime, gs.ActualDelivery, gs.ActualDeliveryTime, gs.CustomerRequest, gs.Notice,  ";
            sql += "gs.Odometer, gs.ServiceDate, gs.VIN, gs.Brand, gs.VehicleName, gs.EngineNo, gs.RegNo,  ";
            sql += "rn.U_KeyNo as KeyNo, '' as ProductionDate, '' as PDIDate, inv.DocDate as InvoiceDate, inv.U_DealerID as Dealer,  ";
            sql += "it.U_Color as ColorCode, gs.CustomerCode as CardCode, gs.CustomerName, rn.LotNumber as LotNo,  ";
            sql += "it.UserText as Remarks,  ms.Name as Model, gs.OtherName as CardFName,gs.CustomerName as CardName,  ";
            sql += "rd.E_Mail, gs.Tel as Phone1, rd.Fax, gs.Mobile as Cellular, gs.ContactPerson as U_ContactPerson,  ";
            sql += "gs.ContactPhone as U_ContactPhone, gs.Address , gs.TaxCode as LicTradNum, rd.Notes,  ";
            sql += " rs.Lamps, rs.Mirrors, rs.WheelsTires, rs.Antenna, rs.Radio, rs.CigarLighter, rs.ToolsSet, rs.Others, ";
            sql += "sd.ItemCode, sd.ItemName, sd.FrgnName, sd.Quantity, sd.Price, sd.VAT, sd.DiscPrcnt, sd.Stock  ";
            sql += "from U_GeneralService gs join U_generalServiceDetail sd on gs.GeneralID = sd.GeneralID  ";
            sql += "left join VSM_ORG..OCRD rd on rd.CardCode = gs.CustomerCode  ";
            sql += "left join VSM_ORG..[@JOBKINDSVC] jk on jk.Code = gs.JobKind ";
            sql += "left join VSM_ORG..[@PAYMENTMETHODSVC] pm on pm.Code = gs.Payment  ";
            sql += "left join OSRN rn on rn.DistNumber = gs.VIN ";
            sql += "left join INV1 inv1 on inv1.ItemCode = rn.ItemCode ";
            sql += "left join OITM it on it.ItemCode = inv1.ItemCode ";
            sql += "left join OINV inv on inv.DocEntry = inv1.BaseEntry ";
            sql += "left join [@ModelSales] ms on ms.Code = it.U_ModelSales ";
            sql += " left join U_ReceptionSheet rs on gs.GeneralID = rs.GeneralID ";
            sql += "where gs.GeneralID = " + GeneralID;
            Services sv = new Services();

            DataSet dsRS = sv.ExecuteQuery("DEALER", sql);
            return dsRS.Tables[0];
        }

        private DataTable getWSC()
        {
            Services sv = new Services();

            string sql = " SELECT [WSCID],gs.[GeneralID],wsc.[RegistrationNum],wsc.[DisCode],wsc.[DisName],wsc.[DealerCode],wsc.[DealerName] ";
            sql += ",wsc.[SeqNo],[InvoiceDate],wsc.[S],wsc.[ID],wsc.[T],[DIV],[ConditionCode],[A],[B],[C],gs.[CustomerName],gs.[VIN],[FailureDate], ";
            sql += "  case [KD] when 1 then 'x' else '' end as 'KD', ";
            sql += "[SoldDate],[HourMeter],[Manu],[RefCode],[M],[COM],[RatioL],[RatioP],[FCT],[Remarks],wsc.[Status], ";
            sql += "ServiceDate, Odometer, gs.Model, EngineNumber, ";
            sql += "ItemCode, ItemName, FrgnName, Quantity, Price, VAT, DiscPrcnt ";
            sql += "FROM [U_WSC] wsc join U_GeneralService gs on wsc.GeneralID = gs.GeneralID ";
            sql += "join U_GeneralServiceDetail det on det.GeneralID = gs.GeneralID ";
            sql += "where wsc.GeneralID = " + GeneralID + " AND det.Status = 0";

            DataSet dsWSC = sv.ExecuteQuery("DEALER", sql);
            return dsWSC.Tables[0];
        }

        private DataTable getWSCB()
        {
            Services sv = new Services();
            string sql = " select RegistrationNum, DisCode, DisName, DealerCode, DealerName, SeqNo, S, ID, T, WSCBRemarks, ";
            sql += " wsc.VIN, case wsc.KD when 1 then 'x' else '' end as KD, wsc.RepairDate, wsc.OrderNum, wsc.Hour, wsc.Amount, ";
            sql += " RIGHT('00' + cast(ROW_NUMBER() OVER (ORDER BY DetID) as nvarchar(2)), 2) AS RowNum ";
            sql += " from U_GeneralService gs left join U_WSCBDetail wsc on gs.GeneralID = wsc.GeneralID ";
            sql += " where wsc.Status = 0 AND gs.GeneralID = " + GeneralID;

            DataSet dsWSC = sv.ExecuteQuery("DEALER", sql);
            return dsWSC.Tables[0];
        }

        private DataTable getQPR()
        {
            Services sv = new Services();

            string sql = " SELECT wsc.[WSCID],gs.[GeneralID],wsc.[RegistrationNum],wsc.[DisCode],wsc.[DisName],";
            sql += " wsc.[DealerCode],wsc.[DealerName]  ,qpr.[SeqNo],gs.VehicleName, wsc.[ConditionCode], gs.[CustomerName],";
            sql += " gs.[VIN],[FailureDate],  [SoldDate], gs.Model, EngineNumber,    ServiceDate, Odometer, gs.Address, ";
            sql += " isnull(qpr.QPRID, -1) QPRID,   [RearBody],[VehicleSpeed],[EngineSpeed],[RoadCondition],";
            sql += " [LoadCondition],[Subject],[UsePurpose],   [Country],[WeatherTemp],isnull([Maintenance], 0) Maintenance, ";
            sql += " isnull([Frequency], 0) Frequency, [Destination],   [PossibleCause],[ActionTaken],[Additional],[VSMComment], ";
            sql += " qpr.[Status] QPRStatus  FROM [U_WSC] wsc join U_GeneralService gs on wsc.GeneralID = gs.GeneralID  ";
            sql += " left join [U_QPR] qpr on qpr.WSCID = wsc.WSCID where wsc.GeneralID = "+ GeneralID;

            DataSet dsWSC = sv.ExecuteQuery("DEALER", sql);
            return dsWSC.Tables[0];
        }

        private DataTable getReservation()
        {
            Services sv = new Services();

            string sql = "SELECT re.[ResID],[SeqNo],[ServiceStaff],[ResDate],[ResTime],[DeliveryDate] ";
            sql += " ,[DeliveryTime],[ConfirmDate],[ConfirmTime],[CustomerCall],[EstimateCost] ";
            sql += " ,[PODate],[POTime],[PADate],[PATime],[CustomerRequest]  ";
            sql += " ,[VehicleID],[VIN],[Brand],[VehicleName],[EngineNo] ";
            sql += " ,[RegNo],[Model],[ColorCode],[Odometer],[CustomerID] ";
            sql += " ,[CustomerName],[OtherName],[Address],[Tel],[Mobile] ";
            sql += " ,[TaxCode],[ContactPerson],[ContactPhone],[DealerCode] ";
            sql += " ,ItemCode, ItemName, FrgnName, Quantity, Price, VAT, DiscPrcnt, Stock ";
            sql += "   FROM [Dealer_Test].[dbo].[U_Reservation] re ";
            sql += "   left join U_ReservationDetail det on re.ResID = det.ResID ";
            sql += " WHERE det.Status = 0 and re.ResID = " + hf_ResID.Value;


            DataSet dsRES = sv.ExecuteQuery("DEALER", sql);
            return dsRES.Tables[0];
        }

        protected void btnPrint_Click(object sender, EventArgs e)
        {
            ReportDocument rptDoc = new ReportDocument();
            rptDoc.Load(Server.MapPath("/Business/Service/Jobcard.rpt"));
            rptDoc.PrintToPrinter(1, false, 0, 0);
        }

        private DataTable getServiceHistory()
        {
            Services sv = new Services();

            string sql = " select ROW_NUMBER() OVER (ORDER BY gs.GeneralID) AS No, gs.GeneralID, gs.JobID, gs.Receptionist, gs.AdviserCode, gs.TechnicianCode, jk.Name as JobKindName, pm.Name as PaymentMethod, ";
            sql += " gs.DateIn, gs.TimeIn, gs.ExpDelivery, gs.ExpTime, gs.StartDate, gs.StartTime,  ";
            sql += " gs.VehicleCode, gs.VIN, gs.CustomerCode,  ";
            sql += " adv.Name AdviserName, tec.Name TechnicianName,   ";
            sql += " gs.FinishDate, gs.FinishTime, gs.ActualDelivery, gs.ActualDeliveryTime, gs.CustomerRequest, gs.Notice,  ";
            sql += " gs.Odometer, gs.ServiceDate, gs.JobKind,  ";
            sql += " gs.VehicleName, gs.Brand, gs.EngineNo, gs.RegNo, gs.Model, gs.ColorCode, gs.CustomerName, gs.OtherName,  ";
            sql += " gs.Address, gs.Tel, gs.TaxCode, gs.ContactPerson, gs.ContactPhone, gs.Mobile, gs.Payment, ";
            sql += " DetailCode, ItemCode, ItemName, FrgnName, Quantity, Price, VAT, DiscPrcnt ";
            sql += " from U_GeneralService gs  ";
            sql += " left join U_GeneralServiceDetail det on gs.GeneralID = det.GeneralID ";
            sql += " left join VSM_ORG..[@JOBKINDSVC] jk on jk.Code = gs.JobKind ";
            sql += " left join VSM_ORG..[@PAYMENTMETHODSVC] pm on pm.Code = gs.Payment  ";
            sql += " left join VSM_ORG..[@TECHNICIAN] adv on gs.AdviserCode = adv.Code  ";
            sql += " left join VSM_ORG..[@TECHNICIAN] tec on gs.TechnicianCode = tec.Code  ";
            sql += " where det.IsSelected = 1 ";
            sql += " AND gs.VIN = '" + hf_VIN.Value + "'";
            DataSet dsSH = sv.ExecuteQuery("DEALER", sql);
            return dsSH.Tables[0];
        }
      
    }
}
